var express = require('express');
var Msdb = require("../../../db/Msdb.js");
var router = express.Router();   //可使用 express.Router 类创建模块化、可挂载的路由句柄
const jwt = require('jsonwebtoken');
var Excel = require('exceljs');
var multer = require('multer');
var UUID = require('uuid');
var request = require("request");
const { getxl_util, getnowdate, saveczlog_util, res_util, checkbysql, addformfield, getnowdate_djh, setformfield,
   insert_table, update_table_wstr, update_table_byid, resutdata_to_formdata, resutdata_to_formdata_arr, save_qlczslog_util,
   usequery } = require('./server_util.js')

const log = require('../../../logger.js');
const db = require('../../../db/Redisdb.js');
var logger = require('../../../logger.js').logger;
const sdk = require('@baiducloud/sdk');
const { sendHttp } = require('./ship_note.js');

//物料档案导入
router.post('/wlda_import', async function (req, res) {
   var data = req.body
   var db = new Msdb()
   let errorRows = []
   let importSql=``
   for (var i = 0; i < data.length; i++) {
      let ifexist = await checkbysql(`select * from shipSRM_wlda where wlbm='${data[i].wlbm}'`)
      if (!ifexist) {
         importSql+=`insert into shipSRM_wlda (id,create_time,create_by,wlbm,wlmc,ggxh,lbbm,je,dw,sfqy)
      values(NEXT VALUE FOR SEQ_ID,getdate(),'admin','${data[i].wlbm}','${data[i].wlmc}','${data[i].ggxh}','${data[i].lbbm}',
      '${data[i].je}','${data[i].dw}','已启用')`
      }
      else{
         errorRows.push({rowNumber:i+2,wlbm:data[i].wlbm})
      }
   }
   await db.update(importSql)
   res_util(res, {errorRows}, true, "导入完成")
})

//查询文档分类
router.post('/queryFileFlList', async function (req, res) {
   var db = new Msdb();
   var querytensql = `select * from  	shipSRM_wdfl   where  jjfl='1'`
   var rtdata = await db.query(querytensql);

   var querytensql2 = `select * from  	shipSRM_wdfl   where  jjfl='2'`
   var rtdata2 = await db.query(querytensql2);

   var result = {
      yjcdList: rtdata.recordsets[0],
      ejcdList: rtdata2.recordsets[0]
   }

   res.send(JSON.stringify(result));

});
//更新历史单据物料名称
router.post('/update_history_wlmc', async function (req, res) {
   var { selectedRows ,storage} = req.body
   
   var sqlstr = `
   --采购管理
update shipSRM_cgdd_mx set wlmc='${selectedRows[0].wlmc}',ggxh='${selectedRows[0].ggxh}' where wlbm = '${selectedRows[0].wlbm}' and (wlmc != '${selectedRows[0].wlmc}' or ggxh != '${selectedRows[0].ggxh}') --采购订单明细
update shipSRM_sltzd_mx set wlmc='${selectedRows[0].wlmc}',ggxh='${selectedRows[0].ggxh}' where wlbm = '${selectedRows[0].wlbm}' and (wlmc != '${selectedRows[0].wlmc}' or ggxh != '${selectedRows[0].ggxh}') --收料通知单明细
update shipSRM_cgrk_detail set wlmc='${selectedRows[0].wlmc}',ggxh='${selectedRows[0].ggxh}' where wlbm = '${selectedRows[0].wlbm}' and (wlmc != '${selectedRows[0].wlmc}' or ggxh != '${selectedRows[0].ggxh}') --采购入库明细
update shipSRM_qgd_mx set wlmc='${selectedRows[0].wlmc}',ggxh='${selectedRows[0].ggxh}' where wlbm = '${selectedRows[0].wlbm}' and (wlmc != '${selectedRows[0].wlmc}' or ggxh != '${selectedRows[0].ggxh}') --请购单明细
update shipSRM_cght_mx set wlmc='${selectedRows[0].wlmc}',ggxh='${selectedRows[0].ggxh}' where wlbm = '${selectedRows[0].wlbm}' and (wlmc != '${selectedRows[0].wlmc}' or ggxh != '${selectedRows[0].ggxh}') --采购合同明细
update shipSRM_cgck_mx set wlmc='${selectedRows[0].wlmc}',ggxh='${selectedRows[0].ggxh}' where wlbm = '${selectedRows[0].wlbm}' and (wlmc != '${selectedRows[0].wlmc}' or ggxh != '${selectedRows[0].ggxh}') --采购出库明细
--销售管理
update shipSRM_xsdd_mx set cpmc='${selectedRows[0].wlmc}',ggxh='${selectedRows[0].ggxh}' where cpbm = '${selectedRows[0].wlbm}' and cpmc != '${selectedRows[0].wlmc}' or ggxh != '${selectedRows[0].ggxh} ' --销售订单明细
update shipSRM_fhtzd_mx set wlmc='${selectedRows[0].wlmc}',ggxh='${selectedRows[0].ggxh}' where wlbm = '${selectedRows[0].wlbm}' and (wlmc != '${selectedRows[0].wlmc}' or ggxh != '${selectedRows[0].ggxh}') --发货通知单明细
update shipSRM_xsckd_mx set wlmc='${selectedRows[0].wlmc}',ggxh='${selectedRows[0].ggxh}' where wlbm = '${selectedRows[0].wlbm}' and (wlmc != '${selectedRows[0].wlmc}' or ggxh != '${selectedRows[0].ggxh}') --销售出库明细
update shipSRM_xsthd_mx set wlmc='${selectedRows[0].wlmc}',ggxh='${selectedRows[0].ggxh}' where wlbm = '${selectedRows[0].wlbm}' and (wlmc != '${selectedRows[0].wlmc}' or ggxh != '${selectedRows[0].ggxh}') --销售退货单明细
--工艺管理
update shipSRM_wlqd_mx set cpmc='${selectedRows[0].wlmc}',ggxh='${selectedRows[0].ggxh}' where cpbm = '${selectedRows[0].wlbm}' and cpmc != '${selectedRows[0].wlmc}' or ggxh != '${selectedRows[0].ggxh}' --物料清单明细
update shipSRM_wlqd set cpmc='${selectedRows[0].wlmc}' where cpbm = '${selectedRows[0].wlbm}' and cpmc != '${selectedRows[0].wlmc}'  --物料清单
--财物管理
update shipSRM_xsfp_mx set wlmc='${selectedRows[0].wlmc}',ggxh='${selectedRows[0].ggxh}' where wlbm = '${selectedRows[0].wlbm}' and (wlmc != '${selectedRows[0].wlmc}' or ggxh != '${selectedRows[0].ggxh}') --销售发票明细
--生产管理
update shipSRM_bgd set wlmc='${selectedRows[0].wlmc}',ggxh='${selectedRows[0].ggxh}' where wlbm = '${selectedRows[0].wlbm}' and (wlmc != '${selectedRows[0].wlmc}' or ggxh != '${selectedRows[0].ggxh}') --报工单
update shipSRM_scjh_mx set cpmc='${selectedRows[0].wlmc}' where wlbm = '${selectedRows[0].wlbm}' and cpmc != '${selectedRows[0].wlmc}'  --生产计划明细
update shipSRM_sclld_mx set wlmc='${selectedRows[0].wlmc}',ggxh='${selectedRows[0].ggxh}' where wlbm = '${selectedRows[0].wlbm}' and (wlmc != '${selectedRows[0].wlmc}' or ggxh != '${selectedRows[0].ggxh}') --生产领料单明细
update shipSRM_scrkd_mx set wlmc='${selectedRows[0].wlmc}',ggxh='${selectedRows[0].ggxh}' where wlbm = '${selectedRows[0].wlbm}' and (wlmc != '${selectedRows[0].wlmc}' or ggxh != '${selectedRows[0].ggxh}') --生产入库单明细
--库存管理
update shipSRM_dbd_mx set wlmc='${selectedRows[0].wlmc}' where wlbm = '${selectedRows[0].wlbm}' and (wlmc != '${selectedRows[0].wlmc}' or ggxh != '${selectedRows[0].ggxh}') --调拨单明细
update shipSRM_qtrk_mx set wlmc='${selectedRows[0].wlmc}',ggxh='${selectedRows[0].ggxh}' where wlbm = '${selectedRows[0].wlbm}' and (wlmc != '${selectedRows[0].wlmc}' or ggxh != '${selectedRows[0].ggxh}') --其他入库明细 
update shipSRM_qtck_mx set wlmc='${selectedRows[0].wlmc}',ggxh='${selectedRows[0].ggxh}' where wlbm = '${selectedRows[0].wlbm}' and (wlmc != '${selectedRows[0].wlmc}' or ggxh != '${selectedRows[0].ggxh}') --其他出库明细
   `
   logger.info(sqlstr)
   await saveczlog_util(sqlstr, storage, '', '更新历史单据物料名称', '更新', '')
   var rtdata = await usequery(sqlstr)
   res_util(res, {}, true, "更新成功")
});


//popview 
router.post('/getpopupview', async function (req, res) {
   var db = new Msdb();
   var viewname = req.body.viewname;
   var checkifviewres = await db.query(`SELECT * FROM sys.views WHERE name = '${viewname}' `)
   if (checkifviewres.recordsets[0].length == 0) {
      res_util(res, [], false, "不是视图查询失败")
   }
   else {
      var rtdata = await db.query(`select  TOP 1000 *  from ${viewname}`)
      res_util(res, rtdata.recordsets[0], true, "查询成功！")
   }

});

//查询销售订单明细通过料号
router.post('/query_crkmx_by_wlbm', async function (req, res) {
   var {selectedRows} = req.body
   var rtdata = await usequery(`select * from v_crkmx_history where wlbm in (${selectedRows.map(item => `'${item.wlbm}'`).join(',')})`)
   res_util(res, rtdata, true, "查询成功！")
});



//查询部门树
router.post('/bmda_tree', async function (req, res) {
   var rtdata = await usequery(`select *,bmmc name,bmbh bm from shipSRM_bmda order by bmbh desc`)
   res_util(res, rtdata, true, "查询成功")
})

//查询物料分类树
router.post('/wlda_tree', async function (req, res) {
   try {
      // 直接查询所有物料分类数据，不做任何处理
      var allData = await usequery(`select * from shipSRM_wlfl order by flbm asc`)
      console.log('查询到的物料分类数据:', allData.length, '条')
      
      res_util(res, allData, true, "查询成功")
   } catch (error) {
      console.error('查询物料分类失败:', error)
      res_util(res, [], false, "查询失败")
   }
})

// 辅助函数：根据分类编码获取父级编码
function getParentFlbm(flbm) {
   if (!flbm || flbm === 'ship') {
      return null
   }
   
   // 检查是否以 'ship.' 开头
   if (!flbm.startsWith('ship.')) {
      console.log(`getParentFlbm: ${flbm} -> 不是有效的分类编码格式`)
      return null
   }
   
   // 移除最后一个层级 (例如: ship.01.02 -> ship.01)
   const parts = flbm.split('.')
   if (parts.length <= 1) {
      return null
   }
   
   const parentFlbm = parts.slice(0, -1).join('.')
   console.log(`getParentFlbm: ${flbm} -> ${parentFlbm}`)
   return parentFlbm
}


//查询物料分类new树
router.post('/wlfllb_new', async function (req, res) {
   var rtdata = await usequery(`select * from shipSRM_wlfl_new order by flbm desc`)
   res_util(res, rtdata, true, "查询成功")
})



//查询物料分类new列表通过id
router.post('/query_wlfl_byid_new', async function (req, res) {
   var data = await usequery(`select * from shipSRM_wlfl_new where id='${req.body.id}' order by flbm desc`)
   res_util(res, { formdata: data[0] }, true, "查询成功")
})


//新增物料分类new
router.post('/add_wlfl_new', async function (req, res) {
   let { formdata, storage } = req.body
   var ifwlbm = await checkbysql(` select top 1 * from shipSRM_wlfl_new where flbm='${formdata.flbm.value}'`)
   if (ifwlbm) {
      res_util(res, { formdata }, false, "新增失败，分类编码已存在")
      return
   }
   //不存在编码插入
   var insrtobj = await insert_table(formdata, "shipSRM_wlfl_new")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增物料分类new', '新增', 'shipSRM_wlfl_new')
   res_util(res, { formdata: insrtobj.data }, true, "新增成功")
})



//更新物料分类new
router.post('/update_wlfl_new', async function (req, res) {
   let { formdata, storage } = req.body
   var ifwlbm = await checkbysql(` select top 1 * from shipSRM_wlfl_new where 
   flbm='${formdata.flbm.value}' and id !='${formdata.id.value}'`)
   if (ifwlbm) {
      res_util(res, { formdata }, false, "更新失败，分类编码已存在")
      return
   }
   //不存在更新
   let updatobj = await update_table_byid(formdata, "shipSRM_wlfl_new")
   await saveczlog_util(updatobj.updatesql, storage, '', '更新物料分类new', '更新', 'shipSRM_wlfl_new')
   res_util(res, { formdata: updatobj.data }, true, "更新成功")
})

//删除物料分类new
router.post('/delete_wlfl_new', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from shipSRM_wlfl_new where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await saveczlog_util(delsql, storage, '', '删除物料分类new', '删除', 'shipSRM_wlfl_new')
   res_util(res, {}, true, "删除成功")
})


//查询物料档案列表
router.post('/wldalb', async function (req, res) {
   var rtdata = await usequery(`select * from shipSRM_wlda order by wlbm desc`)
   res_util(res, rtdata, true, "查询成功")
})

//查询物料档案列表通过id
router.post('/query_wlda_byid', async function (req, res) {
   var data = await usequery(`select * from shipSRM_wlda where id='${req.body.id}' order by wlbm desc`)
   var mxdata = await usequery(`select * from shipSRM_wlda_mx where mainid='${req.body.id}' order by bbh desc`)
   res_util(res, { formdata: data[0],mxdata}, true, "查询成功")
})


//新增物料档案
router.post('/add_wlda', async function (req, res) {
   let { formdata, storage,mxdata} = req.body
   var ifwlbm = await checkbysql(` select top 1 * from shipSRM_wlda where wlbm='${formdata.wlbm.value}'`)
   if (ifwlbm) {
      res_util(res, { formdata }, false, "新增失败，物料编码已存在")
      return
   }
   //不存在编码插入
  let getnextwlbmsql = await usequery(`select top 1 wlbm from shipSRM_wlda where wlbm like '%${formdata.lbbm.value}%' order by wlbm desc`)
 
 console.log("自动料号",getnextwlbmsql)
  let nextwlbm = getnextwlbmsql.length > 0 ? parseInt(getnextwlbmsql[0].wlbm.slice(-4)) + 1 : 1
  formdata.wlbm.value = formdata.lbbm.value + nextwlbm.toString().padStart(4, '0')


   var insrtobj = await insert_table(formdata, "shipSRM_wlda")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增物料档案', '新增', 'shipSRM_wlda')
   formdata = insrtobj.data;
   var mainid = insrtobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = await insert_table(mxitem, "shipSRM_wlda_mx")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增物料档案明细', '新增', 'shipSRM_wlda_mx')                     
   }
   var rtmxdata = await usequery(`select  *  from shipSRM_wlda_mx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: insrtobj.data,mxdata:rtmxdata}, true, "新增成功")
})




//更新物料档案
router.post('/update_wlda', async function (req, res) {
   let { formdata, storage,mxdata,delidarr} = req.body
   var db=new Msdb()
   var ifwlbm = await checkbysql(` select top 1 * from shipSRM_wlda where 
   wlbm='${formdata.wlbm.value}' and id !='${formdata.id.value}'`)
   if (ifwlbm) {
      res_util(res, { formdata }, false, "更新失败，物料编码已存在")
      return
   }
   //不存在更新
   let updatobj = await update_table_byid(formdata, "shipSRM_wlda")
   await saveczlog_util(updatobj.updatesql, storage, '', '更新物料档案', '更新', 'shipSRM_wlda')
   formdata = updatobj.data;
   var mainid = updatobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      var ifcz = await checkbysql(`select * from shipSRM_wlda_mx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "shipSRM_wlda_mx")
      } else {
         rtmxobj = await update_table_byid(mxitem, "shipSRM_wlda_mx")
      }
   }

   //删除delidarr
   await db.update(`delete from shipSRM_wlda_mx where id in (${delidarr.map(item => `'${item}'`).join(',')}) `)
   var rtmxdata = await usequery(`select *  from shipSRM_wlda_mx where mainid ='${formdata.id.value}'`)

   res_util(res, { formdata: updatobj.data,mxdata:rtmxdata}, true, "更新成功")
})

//删除物料档案
router.post('/delete_wlda', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from shipSRM_wlda where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await db.update(`delete from shipSRM_wlda_mx where mainid in (${selectedRows.map(item => `'${item.id}'`).join(',')})`)
   await saveczlog_util(delsql, storage, '', '删除物料档案', '删除', 'shipSRM_wlda')
   res_util(res, {}, true, "删除成功")
})

//启用物料档案
router.post('/enable_wlda', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   for(let i=0; i<selectedRows.length;i++){
      const rtdata = await usequery(`select * from shipSRM_wlda where id='${selectedRows[i].id}'`)
      var sqlupdate=`update shipSRM_wlda set sfqy='已启用' where id='${selectedRows[i].id}'`
      await db.update(sqlupdate)
      await saveczlog_util(sqlupdate, storage, '', '启用物料档案', '修改', 'shipSRM_wlda')
   }
   res_util(res, {}, true, "修改成功")
})

//禁用物料档案
router.post('/disable_wlda', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   for(let i=0; i<selectedRows.length;i++){
      const rtdata = await usequery(`select * from shipSRM_wlda where id='${selectedRows[i].id}'`)
      var sqlupdate=''

      sqlupdate=`update shipSRM_wlda set sfqy='已禁用' where id='${selectedRows[i].id}'`

      await db.update(sqlupdate)
      await saveczlog_util(sqlupdate, storage, '', '禁用物料档案', '修改', 'shipSRM_wlda')
   }
   res_util(res, {}, true, "修改成功")
})



//新增供应商档案
router.post('/add_gys', async function (req, res) {
   let { formdata,ghmxdata,sqmxdata,yhmxdata,zzmxdata,kpmxdata,storage } = req.body
   // var ifwlbm = await checkbysql(` select top 1 * from shipSRM_gys where gysdm='${formdata.gysdm.value}'`)
   // if (ifwlbm) {
   //    res_util(res, { formdata }, false, "新增失败，供应商编码已存在")
   //    return
   // }
   var gysdmData = await usequery(` select top 1 * from shipSRM_gys order by gysdm desc `)
   var nextgysdm = gysdmData.length > 0 ? parseInt(gysdmData[0].gysdm.slice(-5)) + 1 : '10000'
   formdata.gysdm.value = 'GYS' + nextgysdm.toString().padStart(4, '0')
   //不存在编码插入

   var insrtobj = await insert_table(formdata, "shipSRM_gys")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增供应商档案', '新增', 'shipSRM_gys')
   formdata = insrtobj.data;
   var mainid = insrtobj.data.id.value;
   for (var mxkey in ghmxdata) {
      let mxitem = ghmxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = await insert_table(mxitem, "shipSRM_gys_ghmx")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增供应商供货明细', '新增', 'shipSRM_gys_ghmx')                     
   }
   for (var mxkey in sqmxdata) {
      let mxitem = sqmxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = await insert_table(mxitem, "shipSRM_gys_sqmx")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增供应商授权明细', '新增', 'shipSRM_gys_sqmx')                     
   }
   for (var mxkey in yhmxdata) {
      let mxitem = yhmxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = await insert_table(mxitem, "shipSRM_gys_yhmx")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增供应商银行明细', '新增', 'shipSRM_gys_yhmx')                     
   }
   for (var mxkey in zzmxdata) {
      let mxitem = zzmxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = await insert_table(mxitem, "shipSRM_gys_zsmx")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增供应商资质明细', '新增', 'shipSRM_gys_zsmx')                     
   }
   for (var mxkey in kpmxdata) {
      let mxitem = kpmxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = await insert_table(mxitem, "shipSRM_gys_kpmx")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增供应商考评明细', '新增', 'shipSRM_gys_kpmx')                     
   }

   var rtghmxdata = await usequery(`select *  from shipSRM_gys_ghmx where mainid ='${formdata.id.value}'`)
   var rtsqmxdata = await usequery(`select *  from shipSRM_gys_sqmx where mainid ='${formdata.id.value}'`)
   var rtyhmxdata = await usequery(`select *  from shipSRM_gys_yhmx where mainid ='${formdata.id.value}'`)
   var rtzzmxdata = await usequery(`select *  from shipSRM_gys_zsmx where mainid ='${formdata.id.value}'`)
   var rtkpmxdata = await usequery(`select *  from shipSRM_gys_kpmx where mainid ='${formdata.id.value}'`)

   res_util(res, { formdata: formdata,ghmxdata:rtghmxdata,sqmxdata:rtsqmxdata,yhmxdata:rtyhmxdata,zzmxdata:rtzzmxdata,kpmxdata:rtkpmxdata }, true, "新增成功")
})

//更新供应商档案
router.post('/update_gys', async function (req, res) {
   let { formdata,ghmxdata,sqmxdata,yhmxdata,zzmxdata,ghdelidarr,sqdelidarr,yhdelidarr,zzdelidarr, storage } = req.body
   // var ifwlbm = await checkbysql(` select top 1 * from shipSRM_gys where 
   // bm='${formdata.bm.value}' and id !='${formdata.id.value}'`)
   // if (ifwlbm) {
   //    res_util(res, { formdata }, false, "更新失败，物料编码已存在")
   //    return
   // }
   //不存在更新
   let updatobj = await update_table_byid(formdata, "shipSRM_gys")
   await saveczlog_util(updatobj.updatesql, storage, '', '更新供应商档案', '更新', 'shipSRM_gys')

   var mainid = updatobj.data.id.value;
   for (var mxkey in ghmxdata) {
      let mxitem = ghmxdata[mxkey];
      var ifcz = await checkbysql(`select * from shipSRM_gys_ghmx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "shipSRM_gys_ghmx")
      } else {
         rtmxobj = await update_table_byid(mxitem, "shipSRM_gys_ghmx")
      }
   }
   for(var mxkey in sqmxdata){
      let mxitem = sqmxdata[mxkey];
      var ifcz = await checkbysql(`select * from shipSRM_gys_sqmx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "shipSRM_gys_sqmx")
      } else {
         rtmxobj = await update_table_byid(mxitem, "shipSRM_gys_sqmx")
      }
   }
   for(var mxkey in yhmxdata){
      let mxitem = yhmxdata[mxkey];
      var ifcz = await checkbysql(`select * from shipSRM_gys_yhmx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "shipSRM_gys_yhmx")
      } else {
         rtmxobj = await update_table_byid(mxitem, "shipSRM_gys_yhmx")
      }
   }
   for(var mxkey in zzmxdata){
      let mxitem = zzmxdata[mxkey];
      var ifcz = await checkbysql(`select * from shipSRM_gys_zsmx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "shipSRM_gys_zsmx")
      } else {
         rtmxobj = await update_table_byid(mxitem, "shipSRM_gys_zsmx")
      }
   }
   
   //删除delidarr
   await db.update(`delete from shipSRM_gys_ghmx where id in (${ghdelidarr.map(item => `'${item}'`).join(',')}) `)
   await db.update(`delete from shipSRM_gys_sqmx where id in (${sqdelidarr.map(item => `'${item}'`).join(',')}) `)
   await db.update(`delete from shipSRM_gys_yhmx where id in (${yhdelidarr.map(item => `'${item}'`).join(',')}) `)
   await db.update(`delete from shipSRM_gys_zsmx where id in (${zzdelidarr.map(item => `'${item}'`).join(',')}) `)

   var rtghmxdata = await usequery(`select *  from shipSRM_gys_ghmx where mainid ='${formdata.id.value}'`)
   var rtsqmxdata = await usequery(`select *  from shipSRM_gys_sqmx where mainid ='${formdata.id.value}'`)
   var rtyhmxdata = await usequery(`select *  from shipSRM_gys_yhmx where mainid ='${formdata.id.value}'`)
   var rtzzmxdata = await usequery(`select *  from shipSRM_gys_zsmx where mainid ='${formdata.id.value}'`)
   var rtkpmxdata = await usequery(`select *  from shipSRM_gys_kpmx where mainid ='${formdata.id.value}'`)

   
   res_util(res, { formdata: updatobj.data,ghmxdata:rtghmxdata,sqmxdata:rtsqmxdata,yhmxdata:rtyhmxdata,zzmxdata:rtzzmxdata,kpmxdata:rtkpmxdata }, true, "更新成功")
})

//删除供应商档案
router.post('/delete_gys', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from shipSRM_gys where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await saveczlog_util(delsql, storage, '', '删除供应商档案', '删除', 'shipSRM_wlda')
   res_util(res, {}, true, "删除成功")
})



//查询注册申请列表
router.post('/zcxxlb', async function (req, res) {
   var rtdata = await usequery(`select * from shipSRM_zcb order by create_time desc`)
   res_util(res, rtdata, true, "查询成功")
})

//新增车间档案
router.post('/add_zcxx', async function (req, res) {
   let { formdata, storage } = req.body
   var ifbm = await checkbysql(` select top 1 * from shipSRM_zcb where dh='${formdata.dh.value}'`)
   if (ifbm) {
      res_util(res, { formdata }, false, "新增失败，已经存在申请记录")
      return
   }
   //不存在编码插入
   var insrtobj = await insert_table(formdata, "shipSRM_zcb")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增注册申请', '新增', 'shipSRM_zcb')
   res_util(res, { formdata: insrtobj.data }, true, "新增成功")
})

//查询注册申请列表通过id
router.post('/query_zcxx_byid', async function (req, res) {
   var data = await usequery(`select * from shipSRM_zcb where id='${req.body.id}' order by create_time desc`)
   res_util(res, {formdata:data[0]}, true, "查询成功")
})


//新增车间档案
router.post('/update_zcxx', async function (req, res) {
   let { formdata, storage } = req.body
   var ifbm = await checkbysql(` select top 1 * from shipSRM_zcb where dh='${formdata.dh.value}'`)
   if (ifbm) {
      res_util(res, { formdata }, false, "更新失败，已经存在更新记录")
      return
   }
   
   //不存在更新
   let updatobj = await update_table_byid(formdata, "shipSRM_zcb")
   await saveczlog_util(updatobj.updatesql, storage, '', '更新注册信息', '更新', 'shipSRM_zcb')
   res_util(res, { formdata: updatobj.data }, true, "更新成功")
})


//驳回注册申请
router.post('/refuse_zcxx', async function (req, res) {
   let { formdata, storage } = req.body
   formdata.clzt.value  = "已驳回"
   let updatobj = await update_table_byid(formdata, "shipSRM_zcb")
   //发送驳回消息的短信并调用短信平台（Msg = "您的申请已被驳回，请重新申请"）
//    const config = {
//       credentials: {

//         //aksk 需要在申请后进行替换
//           ak: 'ALTAKeJlSZgJJDpTGyKbvPCiSw',
//           sk: '182e0fa511b944dfa4b5763fa989e3e2'
//       },
//       endpoint: 'http://smsv3.bj.baidubce.com'
//   };

//       let fhxx = ''
//       let fhid = ''
//      //发送短信开始  启动云服务客户端
//       let client = new sdk.HttpClient(config);
      
//       //拼接多个请求字节头
//       let path = "/api/v3/sendSms";
//       let body = JSON.stringify({
//           "mobile": formdata.dh.value,
//           //短信签名需要替换为拒绝签名
//           "signatureId": "sms-sign-SRNNYU81891",
//           //短信模板需要替换为拒绝模板
//           "template": "sms-tmpl-axaHQQ35906",

//           //入参变量
//           "contentVar": {
//               "xm": formdata.gysmc.value,
//           }
//       })
//       let params = {"clientToken": ""};
//       let headers = {"Content-Type": "application/json", "Accept": "application/json"};

//      //发送请求 客户端调度命令
//       await client.sendRequest("POST", path, body, headers, params)
//           .then(function (response) {
//               console.log("object: ", response.body)
//               fhxx = response.body.message
//               fhid = response.body.requestId
//           }, function (error) {
//               console.log("error: ", error)
//           });

//           formdata.fhxx.value = fhxx
//           formdata.fhid.value = fhid
          
          await update_table_byid(formdata, "shipSRM_zcb")

   await saveczlog_util(updatobj.updatesql, storage, '', '驳回注册申请', '修改', 'shipSRM_zcb')

   res_util(res, { formdata }, true, "驳回成功")
})

//通过并创建账号
router.post('/pass_zcxx', async function (req, res) {
   let { formdata,ryformdata,gysformdata, storage } = req.body
   formdata.clzt.value  = "已通过"
   console.log("formdata================",formdata)
   // 生成6位随机密码
   function generateRandomPassword() {
      let password = '';
      for(let i = 0; i < 6; i++) {
         password += Math.floor(Math.random() * 10);
      }
      return password;
   }
   //修改注册申请状态
   

   //发送通过消息的短信并调用短信平台（Msg = "您的申请已通过，请使用账号和密码登录系统，系统地址为"）
  
      // const config = {
      //     credentials: {

      //       //aksk 需要在申请后进行替换
      //         ak: 'ALTAKeJlSZgJJDpTGyKbvPCiSw',
      //         sk: '182e0fa511b944dfa4b5763fa989e3e2'
      //     },
      //     endpoint: 'http://smsv3.bj.baidubce.com'
      // };

      var gysdmData = await usequery(` select top 1 * from shipSRM_gys order by gysdm desc `)
      var nextgysdm = gysdmData.length > 0 ? parseInt(gysdmData[0].gysdm.slice(-5)) + 1 : '10000'
      var gysdm = 'GYS' + nextgysdm.toString().padStart(5, '0')
   

      ryformdata.zh.value = gysdm
      ryformdata.mm.value = generateRandomPassword();
      ryformdata.ryzt.value = "正常"
      ryformdata.yhlx.value = '租户用户'
      ryformdata.dh.value = formdata.dh.value
      ryformdata.xm.value = formdata.gysmc.value
      ryformdata.js.value = "供应商【临时】"
      ryformdata.glgys.value = formdata.gysmc.value
      ryformdata.jsid.value = 1002277
      
      let msg = `您的申请已通过，请使用账号${gysdm}和密码${ryformdata.mm.value}登录系统，系统地址为http://111.77.154.58:2233`
      let msgresult = await sendHttp(formdata.dh.value,msg)
      let id = await getxl_util()
      await usequery(`insert into shipSRM_fsjl(id,create_time,create_by,phone,dxnr,fszt,tjsj,uid) 
         values('${id}',getdate(),'${storage.ygxm}','${formdata.dh.value}','${msg}','发送中',getdate(),'${JSON.parse(msgresult).uid}')`)
   
      gysformdata.gysmc.value = formdata.gysmc.value
      gysformdata.hzzt.value = "准入中"
      gysformdata.xysbdm.value = formdata.xysbdm.value
      gysformdata.gysdm.value = gysdm
      gysformdata.sj.value = formdata.dh.value
      // gysformdata.gysdcbmb.value = "供应商调查表.docx"
      

          let fhxx = ''
          let fhid = ''
         //发送短信开始  启动云服务客户端
         //  let client = new sdk.HttpClient(config);
          
         //  //拼接多个请求字节头
         //  let path = "/api/v3/sendSms";
         //  let body = JSON.stringify({
         //      "mobile": formdata.dh.value,
         //      //短信签名需要替换
         //      "signatureId": "sms-sign-SRNNYU81891",
         //      //短信模板需要替换
         //      "template": "sms-tmpl-axaHQQ35906",

         //      //入参变量
         //      "contentVar": {
         //          "xm": formdata.gysmc.value,
         //          "zh": ryformdata.zh.value,
         //          "mm": ryformdata.mm.value,
         //          "fwdz": "http://192.168.1.100:8080/shipSRM/login"
         //      }
         //  })
         //  let params = {"clientToken": ""};
         //  let headers = {"Content-Type": "application/json", "Accept": "application/json"};

         // //发送请求 客户端调度命令
         //  await client.sendRequest("POST", path, body, headers, params)
         //      .then(function (response) {
         //          console.log("object: ", response.body)
         //          fhxx = response.body.message
         //          fhid = response.body.requestId
         //      }, function (error) {
         //          console.log("error: ", error)
         //      });

         //      formdata.fhxx.value = fhxx
         //      formdata.fhid.value = fhid
              await update_table_byid(formdata, "shipSRM_zcb")
  
   //发送短信结束
 
   
  

   

   var insrtobj = await insert_table(ryformdata, "shipSRM_ryda")
   var insrtobj2 = await insert_table(gysformdata, "shipSRM_gys")
   await saveczlog_util(insrtobj.insertsql, storage, '', '注册申请审批通过并创建账号', '新增', 'shipSRM_ryda')
   await saveczlog_util(insrtobj2.insertsql, storage, '', '注册申请审批通过并创建账号', '新增', 'shipSRM_gys')

   var sql = `MERGE into  shipSRM_sccd  a USING 
	(SELECT c.id+a.id as id,a.id as userid,c.cdqx,d.cdly,d.cdmc,d.cdlx,c.update_time,
		c.sfsc from shipSRM_ryda a,shipSRM_js b,shipSRM_js_mx c,shipSRM_bgmenu d where a.jsid=b.id 
		and b.id=c.mainid and c.ssmk='PC' and d.jjcd='2' and d.cdbh=c.cdqx ) b on b.id=a.id
		when matched then UPDATE SET  cdmc=b.cdmc,cdly=b.cdly,cdqx=b.cdqx
		WHEN NOT MATCHED THEN  INSERT (id, mainid, sfsc,cdmc,cdqx,cdly,update_time,ssmk)  VALUES (b.id, b.userid,'否' ,b.cdmc,b.cdqx,b.cdly,b.update_time,'PC')
      WHEN NOT MATCHED BY SOURCE THEN  DELETE;`
   var result = await usequery(sql);
   

   res_util(res, { formdata }, true, "注册申请受理通过并创建账号成功")
})


//查询人员档案列表
router.post('/rylb', async function (req, res) {
   var rtdata = await usequery(`select *,
      ROW_NUMBER() OVER (PARTITION BY bmmc ORDER BY bmmc) as bmmc_row,
      CASE WHEN ROW_NUMBER() OVER (PARTITION BY bmmc ORDER BY bmmc) = 1 
         THEN bmmc ELSE NULL END AS bmmcq,
      CASE WHEN ROW_NUMBER() OVER (PARTITION BY bmmc ORDER BY bmmc) = 1 
         THEN gw ELSE NULL END AS gwq
      from shipSRM_ryda where ryzt='${req.body.type}' order by bmmc,gw desc `)
   res_util(res, rtdata, true, "查询成功")
})

//查询人员档案列表通过id
router.post('/query_ry_byid', async function (req, res) {
   var data = await usequery(`select * from shipSRM_ryda where id='${req.body.id}' order by rzsj desc`)
   res_util(res, {formdata:data[0]}, true, "查询成功")
})

//新增人员档案
router.post('/add_ry', async function (req, res) {
   let { formdata, storage } = req.body
   var ifwlbm = await checkbysql(` select top 1 * from shipSRM_ryda where zh='${formdata.zh.value}'`)
   if (ifwlbm) {
      res_util(res, { formdata }, false, "新增失败，账号已存在")
      return
   }
   //不存在编码插入
   var insrtobj = await insert_table(formdata, "shipSRM_ryda")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增人员档案', '新增', 'shipSRM_ryda')
   res_util(res, { formdata: insrtobj.data }, true, "新增成功")
})

//更新人员档案
router.post('/update_ry', async function (req, res) {
   let { formdata, storage } = req.body
   var ifwlbm = await checkbysql(` select top 1 * from shipSRM_ryda where 
   zh='${formdata.zh.value}' and id !='${formdata.id.value}'`)
   if (ifwlbm) {
      res_util(res, { formdata }, false, "更新失败，账号已存在")
      return
   }
   //不存在更新
   let updatobj = await update_table_byid(formdata, "shipSRM_ryda")
   await saveczlog_util(updatobj.updatesql, storage, '', '更新人员档案', '更新', 'shipSRM_ryda')
   res_util(res, { formdata: updatobj.data }, true, "更新成功")
})


//删除人员档案
router.post('/delete_ry', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from shipSRM_ryda where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await saveczlog_util(delsql, storage, '', '删除人员档案', '删除', 'shipSRM_ryda')
   res_util(res, {}, true, "删除成功")
})

//禁用人员档案
router.post('/disable_ry', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `update shipSRM_ryda set ryzt='禁用',lzsj=getdate() where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await saveczlog_util(delsql, storage, '', '禁用人员档案', '修改', 'shipSRM_ryda')
   res_util(res, {}, true, "禁用成功")
})

//启用人员档案
router.post('/able_ry', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `update shipSRM_ryda set ryzt='正常',rzsj=getdate() where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await saveczlog_util(delsql, storage, '', '启用人员档案', '修改', 'shipSRM_ryda')
   res_util(res, {}, true, "启用成功")
})

//查询部门档案列表
router.post('/bmlb', async function (req, res) {
   var rtdata = await usequery(`select * from shipSRM_bmda order by create_time desc`)
   res_util(res, rtdata, true, "查询成功")
})

//查询部门档案列表通过id
router.post('/query_bm_byid', async function (req, res) {
   var data = await usequery(`select * from shipSRM_bmda where id='${req.body.id}' order by create_time desc`)
   res_util(res, {formdata:data[0]}, true, "查询成功")
})

//新增部门档案
router.post('/add_bm', async function (req, res) {
   let { formdata, storage } = req.body
   var ifwlbm = await checkbysql(` select top 1 * from shipSRM_bmda where bmbh='${formdata.bmbh.value}'`)
   if (ifwlbm) {
      res_util(res, { formdata }, true, "新增失败，部门编号已存在")
      return
   }
   //不存在编码插入
   var insrtobj = await insert_table(formdata, "shipSRM_bmda")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增部门档案', '新增', 'shipSRM_bmda')
   res_util(res, { formdata: insrtobj.data }, true, "新增成功")
})

//更新部门档案
router.post('/update_bm', async function (req, res) {
   let { formdata, storage } = req.body
   var ifwlbm = await checkbysql(` select top 1 * from shipSRM_bmda where 
   bmbh='${formdata.bmbh.value}' and id !='${formdata.id.value}'`)
   if (ifwlbm) {
      res_util(res, { formdata }, false, "更新失败，部门编号已存在")
      return
   }
   //不存在更新
   let updatobj = await update_table_byid(formdata, "shipSRM_bmda")
   await saveczlog_util(updatobj.updatesql, storage, '', '更新部门档案', '更新', 'shipSRM_bmda')
   res_util(res, { formdata: updatobj.data }, true, "更新成功")
})

//删除部门档案
router.post('/delete_bm', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from shipSRM_bmda where id = '${selectedRows.id}'`
   await db.update(delsql)
   await saveczlog_util(delsql, storage, '', '删除部门档案', '删除', 'shipSRM_wlda')
   res_util(res, {}, true, "删除成功")
})


//导入客户信息 excel

router.post('/khda_import', async function (req, res) {
   var data=req.body
   var db=new Msdb()
  for(var i=0;i<data.length;i++){

      
  await db.update(`insert into shipSRM_khda(id,create_time,create_by,mc,lxr,dh,dz,bm)
    values(NEXT VALUE FOR SEQ_ID,getdate(),'admin','${data[i].mc}','${data[i].lxr}','${data[i].dh}','${data[i].dz}','${data[i].bm}')`)
   
  }


   res_util(res, {}, true, "导入完成")
})

//查询客户档案列表
router.post('/khdalb', async function (req, res) {
   var rtdata = await usequery(`select * from shipSRM_khda order by bm desc`)
   res_util(res, rtdata, true, "查询成功")
})


//查询客户档案列表通过id
router.post('/query_khda_byid', async function (req, res) {
   var data = await usequery(`select * from shipSRM_khda where id='${req.body.id}' order by bm desc`)
   res_util(res, { formdata: data[0] }, true, "查询成功")
})

//新增客户档案
router.post('/add_khda', async function (req, res) {
   let { formdata, storage } = req.body
   var ifbm = await checkbysql(` select top 1 * from shipSRM_khda where bm='${formdata.bm.value}'`)
   if (ifbm) {
      res_util(res, { formdata }, false, "新增失败，客户编码已存在")
      return
   }
   //不存在编码插入
   var insrtobj = await insert_table(formdata, "shipSRM_khda")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增客户档案', '新增', 'shipSRM_khda')
   res_util(res, { formdata: insrtobj.data }, true, "新增成功")
})

//更新客户档案
router.post('/update_khda', async function (req, res) {
   let { formdata, storage } = req.body
   var ifbm = await checkbysql(` select top 1 * from shipSRM_khda where 
   bm='${formdata.bm.value}' and id !='${formdata.id.value}'`)
   if (ifbm) {
      res_util(res, { formdata }, false, "更新失败，客户编码已存在")
      return
   }
   //不存在更新
   let updatobj = await update_table_byid(formdata, "shipSRM_khda")
   await saveczlog_util(updatobj.updatesql, storage, '', '更新客户档案', '更新', 'shipSRM_khda')
   res_util(res, { formdata: updatobj.data }, true, "更新成功")
})

//删除客户档案
router.post('/delete_khda', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from shipSRM_khda where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await saveczlog_util(delsql, storage, '', '删除客户档案', '删除', 'shipSRM_khda')
   res_util(res, {}, true, "删除成功")
})

//查询车间档案列表
router.post('/cjdalb', async function (req, res) {
   var rtdata = await usequery(`select * from shipSRM_cjda order by create_time desc`)
   res_util(res, rtdata, true, "查询成功")
})

//查询车间档案列表通过id
router.post('/query_cjda_byid', async function (req, res) {
   var data = await usequery(`select * from shipSRM_cjda where id='${req.body.id}' order by create_time desc`)
   res_util(res, { formdata: data[0] }, true, "查询成功")
})

//新增车间档案
router.post('/add_cjda', async function (req, res) {
   let { formdata, storage } = req.body
   var ifbm = await checkbysql(` select top 1 * from shipSRM_cjda where cjbm='${formdata.cjbm.value}'`)
   if (ifbm) {
      res_util(res, { formdata }, false, "新增失败，车间编码已存在")
      return
   }
   //不存在编码插入
   var insrtobj = await insert_table(formdata, "shipSRM_cjda")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增车间档案', '新增', 'shipSRM_cjda')
   res_util(res, { formdata: insrtobj.data }, true, "新增成功")
})

//更新车间档案
router.post('/update_cjda', async function (req, res) {
   let { formdata, storage } = req.body
   var ifbm = await checkbysql(` select top 1 * from shipSRM_cjda where 
   cjbm='${formdata.cjbm.value}' and id !='${formdata.id.value}'`)
   if (ifbm) {
      res_util(res, { formdata }, false, "更新失败，车间编码已存在")
      return
   }
   //不存在更新
   let updatobj = await update_table_byid(formdata, "shipSRM_cjda")
   await saveczlog_util(updatobj.updatesql, storage, '', '更新车间档案', '更新', 'shipSRM_cjda')
   res_util(res, { formdata: updatobj.data }, true, "更新成功")
})

//删除车间档案
router.post('/delete_cjda', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from shipSRM_cjda where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await saveczlog_util(delsql, storage, '', '删除车间档案', '删除', 'shipSRM_cjda')
   res_util(res, {}, true, "删除成功")
})


//查询物料分类树
router.post('/wlfllb', async function (req, res) {
   var rtdata = await usequery(`select * from shipSRM_wlfl order by flbm asc`)
   res_util(res, rtdata, true, "查询成功")
})


//查询物料分类列表通过id
router.post('/query_wlfl_byid', async function (req, res) {
   var data = await usequery(`select * from shipSRM_wlfl where id='${req.body.id}' order by create_time desc`)
   res_util(res, { formdata: data[0] }, true, "查询成功")
})

//新增物料分类
router.post('/add_wlfl', async function (req, res) {
   let { formdata, storage } = req.body
   var ifbm = await checkbysql(` select top 1 * from shipSRM_wlfl where flbm='${formdata.flbm.value}'`)
   if (ifbm) {
      res_util(res, { formdata }, false, "新增失败，物料分类编码已存在")
      return
   }
   //不存在编码插入
   var insrtobj = await insert_table(formdata, "shipSRM_wlfl")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增物料分类', '新增', 'shipSRM_wlfl')
   res_util(res, { formdata: insrtobj.data }, true, "新增成功")
})

//新增一级分类
router.post('/add_wlfl_yjfl', async function (req, res) {
   let { flbm,flmc, storage } = req.body
   let db=new Msdb()
   var ifbm = await checkbysql(` select top 1 * from shipSRM_wlfl where flbm='${flbm}'`)
   if (ifbm) {
      res_util(res, { }, false, "新增失败，物料分类编码已存在")
      return
   }
   //不存在编码插入
   let id=await getxl_util()
   let sql=`insert into shipSRM_wlfl(id,create_time,create_by,flbm,flmc,sjflid) values('${id}',getdate(),'${storage.ygxm}','${flbm}','${flmc}','000')`
   await db.update(sql)
   await saveczlog_util(sql, storage, '', '新增物料分类', '新增', 'shipSRM_wlfl')
   res_util(res, {}, true, "新增成功")
})

//新增二级分类
router.post('/add_wlfl_zjfl', async function (req, res) {
   let { flbm, flmc, storage } = req.body
   let db=new Msdb()
   var ifbm = await checkbysql(` select top 1 * from shipSRM_wlfl where flbm='${flbm}'`)
   if (ifbm) {
         res_util(res, { }, false, "新增失败，物料分类编码已存在")
      return
   }
   
   // 根据编码规则获取父级信息
   let sjflbm = ''
   let sjflmc = ''
   let sjflid = ''
   
   if (flbm.includes('.')) {
     const parts = flbm.split('.')
     parts.pop() // 移除最后一部分
     sjflbm = parts.join('.')
     
     // 查找父级分类
     var sjfldata = await usequery(`select id, flmc from shipSRM_wlfl where flbm='${sjflbm}'`)
     if (sjfldata.length > 0) {
       sjflid = sjfldata[0].id
       sjflmc = sjfldata[0].flmc
     } else {
       res_util(res, { }, false, "新增失败，未找到父级分类")
       return
     }
   } else {
     res_util(res, { }, false, "新增失败，分类编码格式不正确")
     return
   }
   
   //不存在编码插入
   let id=await getxl_util()
   let sql=`insert into shipSRM_wlfl(id,create_time,create_by,flbm,flmc,sjflid,sjflbm,sjflmc) values('${id}',getdate(),'${storage.ygxm}','${flbm}','${flmc}','${sjflid}','${sjflbm}','${sjflmc}')`
   await db.update(sql)
   await saveczlog_util(sql, storage, '', '新增物料分类', '新增', 'shipSRM_wlfl')
   
   res_util(res, {}, true, "新增成功")
})

//更新物料分类
router.post('/update_wlfl', async function (req, res) {
   let { formdata, storage } = req.body
   let db = new Msdb()
   
   // 检查要修改的分类是否存在
   var existingCategory = await usequery(`select * from shipSRM_wlfl where id='${formdata.id.value}'`)
   if (existingCategory.length === 0) {
      res_util(res, { formdata }, false, "更新失败，未找到要修改的分类")
      return
   }
   
   // 检查分类名称是否与其他分类重复（排除当前分类）
   var duplicateName = await checkbysql(` select top 1 * from shipSRM_wlfl where 
   flmc='${formdata.flmc.value}' and id !='${formdata.id.value}'`)
   if (duplicateName) {
      res_util(res, { formdata }, false, "更新失败，分类名称已存在")
      return
   }
   
   // 更新分类信息
   let updatobj = await update_table_byid(formdata, "shipSRM_wlfl")
   await saveczlog_util(updatobj.updatesql, storage, '', '更新物料分类', '更新', 'shipSRM_wlfl')
   res_util(res, { formdata: updatobj.data }, true, "更新成功")
})

//删除物料分类
router.post('/delete_wlfl', async function (req, res) {
   let { selectedKeys, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from shipSRM_wlfl where flbm='${selectedKeys}'`
   await db.update(delsql)
   await saveczlog_util(delsql, storage, '', '删除物料分类', '删除', 'shipSRM_wlfl')
   res_util(res, {}, true, "删除成功")
})

//查询产线档案列表
router.post('/cxdalb', async function (req, res) {
   var rtdata = await usequery(`select * from shipSRM_cxda order by create_time desc`)
   res_util(res, rtdata, true, "查询成功")
})

//查询产线档案列表通过id
router.post('/query_cxda_byid', async function (req, res) {
   var data = await usequery(`select * from shipSRM_cxda where id='${req.body.id}' order by create_time desc`)
   res_util(res, { formdata: data[0] }, true, "查询成功")
})

//新增产线档案
router.post('/add_cxda', async function (req, res) {
   let { formdata, storage } = req.body
   var ifbm = await checkbysql(` select top 1 * from shipSRM_cxda where cxbm='${formdata.cxbm.value}'`)
   if (ifbm) {
      res_util(res, { formdata }, false, "新增失败，产线编码已存在")
      return
   }
   //不存在编码插入
   var insrtobj = await insert_table(formdata, "shipSRM_cxda")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增产线档案', '新增', 'shipSRM_cxda')
   res_util(res, { formdata: insrtobj.data }, true, "新增成功")
})

//更新产线档案
router.post('/update_cxda', async function (req, res) {
   let { formdata, storage } = req.body
   var ifbm = await checkbysql(` select top 1 * from shipSRM_cxda where 
   cxbm='${formdata.cxbm.value}' and id !='${formdata.id.value}'`)
   if (ifbm) {
      res_util(res, { formdata }, false, "更新失败，产线编码已存在")
      return
   }
   //不存在更新
   let updatobj = await update_table_byid(formdata, "shipSRM_cxda")
   await saveczlog_util(updatobj.updatesql, storage, '', '更新产线档案', '更新', 'shipSRM_cxda')
   res_util(res, { formdata: updatobj.data }, true, "更新成功")
})

//删除产线档案
router.post('/delete_cxda', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from shipSRM_cxda where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await saveczlog_util(delsql, storage, '', '删除产线档案', '删除', 'shipSRM_cxda')
   res_util(res, {}, true, "删除成功")
})


//查询仓库档案列表
router.post('/ckdalb', async function (req, res) {
   var rtdata = await usequery(`select * from shipSRM_ckda order by ckbm desc`)
   res_util(res, rtdata, true, "查询成功")
})

//查询仓库档案列表通过id
router.post('/query_ckda_byid', async function (req, res) {
   var data = await usequery(`select * from shipSRM_ckda where id='${req.body.id}' order by create_time desc`)
   res_util(res, { formdata: data[0] }, true, "查询成功")
})

//新增仓库档案
router.post('/add_ckda', async function (req, res) {
   let { formdata, storage } = req.body
   var ifbm = await checkbysql(` select top 1 * from shipSRM_ckda where ckbm='${formdata.ckbm.value}'`)
   if (ifbm) {
      res_util(res, { formdata }, true, "新增失败，仓库编码已存在")
      return
   }
   //不存在编码插入
   var insrtobj = await insert_table(formdata, "shipSRM_ckda")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增仓库档案', '新增', 'shipSRM_ckda')
   res_util(res, { formdata: insrtobj.data }, true, "新增成功")
})

//更新仓库档案
router.post('/update_ckda', async function (req, res) {
   let { formdata, storage } = req.body
   var ifbm = await checkbysql(` select top 1 * from shipSRM_ckda where 
   ckbm='${formdata.ckbm.value}' and id !='${formdata.id.value}'`)
   if (ifbm) {
      res_util(res, { formdata }, false, "更新失败，物料编码已存在")
      return
   }
   //不存在更新
   let updatobj = await update_table_byid(formdata, "shipSRM_ckda")
   await saveczlog_util(updatobj.updatesql, storage, '', '更新仓库档案', '更新', 'shipSRM_ckda')
   res_util(res, { formdata: updatobj.data }, true, "更新成功")
})

//删除仓库档案
router.post('/delete_ckda', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from shipSRM_ckda where id = '${selectedRows.id}'`
   await db.update(delsql)
   await saveczlog_util(delsql, storage, '', '删除仓库档案', '新增', 'shipSRM_ckda')
   res_util(res, {}, true, "删除成功")
})



//查询角色数据
router.post('/queryJsDataSource',async function(req,res){
      var rtdata=await usequery(`select * from shipSRM_js`);
      res_util(res, rtdata, true, "查询成功")
   });

//查询物料数据
router.post('/queryWlmcDataSource',async function(req,res){
   var rtdata=await usequery(`select * from shipSRM_wlda`);
   res_util(res, rtdata, true, "查询成功")
});

//查询仓库数据
router.post('/queryCkDataSource',async function(req,res){
   var rtdata=await usequery(`select *  from shipSRM_ckda`);
   res_util(res, rtdata, true, "查询成功")
});

//查询产品档案列表
router.post('/cpdalb', async function (req, res) {
   let {storage } = req.body
   var rtdata = await usequery(`select * from shipSRM_cpda order by create_time desc`)
   res_util(res, rtdata, true, "查询成功")
})

//查询产品档案列表通过id
router.post('/query_cpda_byid', async function (req, res) {
   var data = await usequery(`select * from shipSRM_cpda where id='${req.body.data.id}' order by create_time desc`)
   var mxdata = await usequery(`select b.* from shipSRM_wlqd a,shipSRM_wlqd_mx b where a.id=b.mainid and a.cpbm='${data[0].wlbm}'`)
   var gylxmxdata = await usequery(`select b.* from shipSRM_gylx a,shipSRM_gylx_gymx b where a.id=b.mainid and a.cpbm='${data[0].wlbm}' order by b.sx asc`)
   res_util(res, { formdata: data[0],mxdata,gylxmxdata}, true, "查询成功")
})

//新增产品档案
router.post('/add_cpda', async function (req, res) {
   let { formdata, mxdata, storage } = req.body
   var insrtobj = await insert_table(formdata, "shipSRM_cpda")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增产品档案', '新增', 'shipSRM_cpda')
   formdata = insrtobj.data;
   var mainid = insrtobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = await insert_table(mxitem, "shipSRM_cpda_tzbgmx")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增产品档案明细', '新增', 'shipSRM_cpda_tzbgmx')                     
   }
   var rtmxdata = await usequery(`select top 1 *  from shipSRM_cpda_tzbgmx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, `新增产品档案成功`)
})

//更新产品档案
router.post('/update_cpda', async function (req, res) {
   let db=new Msdb()
   let { formdata, mxdata, storage, delidarr } = req.body
   let updatobj = await update_table_byid(formdata, "shipSRM_cpda")
   await saveczlog_util(updatobj.updatesql, storage, '', '修改产品档案', '修改', 'shipSRM_cpda')
   formdata = updatobj.data;
   var mainid = updatobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      var ifcz = await checkbysql(`select * from shipSRM_cpda_tzbgmx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "shipSRM_cpda_tzbgmx")
      } else {
         rtmxobj = await update_table_byid(mxitem, "shipSRM_cpda_tzbgmx")
      }
   }
   
   //删除delidarr
   await db.update(`delete from shipSRM_cpda_tzbgmx where id in (${delidarr.map(item => `'${item}'`).join(',')}) `)
   var rtmxdata = await usequery(`select *  from shipSRM_cpda_tzbgmx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, "更新产品档案成功")
})

//  删除产品档案
router.post('/delete_cpda', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from shipSRM_cpda where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await db.update(`delete from shipSRM_cpda_tzbgmx where mainid in (${selectedRows.map(item => `'${item.id}'`).join(',')})`)
   await saveczlog_util(delsql, storage, '', '删除产品档案', '删除', 'shipSRM_cpda')
   res_util(res, {}, true, "删除成功")
})

//修改物料分类名称
router.post('/update_wlfl_name', async function (req, res) {
   let { flbm, flmc, storage } = req.body
   let db = new Msdb()
   
   // 检查要修改的分类是否存在
   var existingCategory = await usequery(`select * from shipSRM_wlfl where flbm='${flbm}'`)
   if (existingCategory.length === 0) {
      res_util(res, {}, false, "修改失败，未找到要修改的分类")
      return
   }
   
   // 检查分类名称是否与其他分类重复（排除当前分类）
   var duplicateName = await checkbysql(` select top 1 * from shipSRM_wlfl where 
   flmc='${flmc}' and flbm !='${flbm}'`)
   if (duplicateName) {
      res_util(res, {}, false, "修改失败，分类名称已存在")
      return
   }
   
   // 更新分类名称
   let sql = `update shipSRM_wlfl set flmc='${flmc}', update_time=getdate(), update_by='${storage.ygxm}' where flbm='${flbm}'`
   await db.update(sql)
   await saveczlog_util(sql, storage, '', '修改物料分类名称', '更新', 'shipSRM_wlfl')
   res_util(res, {}, true, "修改分类名称成功")
})

//检查分类是否可以删除
router.post('/check_category_deletable', async function (req, res) {
   let { flbm } = req.body
   
   try {
      // 1. 检查当前分类下是否有子级分类
      var childCategories = await usequery(`select flbm, flmc from shipSRM_wlfl where flbm like '${flbm}.%'`)
      
      if (childCategories.length > 0) {
         const childNames = childCategories.map(item => item.flmc).join('、')
         res_util(res, {}, false, `无法删除，该分类下还有子级分类：${childNames}`)
         return
      }
      
      // 2. 检查当前分类下是否有物料
      var categoryMaterials = await usequery(`select count(*) as count from shipSRM_wlda where lbbm like '%${flbm}%'`)
      if (categoryMaterials[0].count > 0) {
         res_util(res, {}, false, `无法删除，该分类下还有 ${categoryMaterials[0].count} 个物料`)
         return
      }
      
      // 3. 检查所有子级分类下是否有物料（一次性查询所有子分类）
      var allChildCategories = await usequery(`select flbm, flmc from shipSRM_wlfl where flbm like '${flbm}.%'`)
      
      for (let child of allChildCategories) {
         // 检查当前子分类下的物料
         const materials = await usequery(`select count(*) as count from shipSRM_wlda where lbbm like '%${child.flbm}%'`)
         if (materials[0].count > 0) {
            res_util(res, {}, false, `无法删除，子级分类"${child.flmc}"下还有 ${materials[0].count} 个物料`)
            return
         }
         
         // 递归检查该子分类的子分类
         const subChildren = await usequery(`select flbm, flmc from shipSRM_wlfl where flbm like '${child.flbm}.%'`)
         for (let subChild of subChildren) {
            const subMaterials = await usequery(`select count(*) as count from shipSRM_wlda where lbbm like '%${subChild.flbm}%'`)
            if (subMaterials[0].count > 0) {
               res_util(res, {}, false, `无法删除，子级分类"${subChild.flmc}"下还有 ${subMaterials[0].count} 个物料`)
               return
            }
         }
      }
      
      // 所有检查都通过，可以删除
      res_util(res, {}, true, "可以删除")
      
   } catch (error) {
      console.error('检查分类删除状态失败:', error)
      res_util(res, {}, false, "检查失败，请稍后重试")
   }
})

//删除物料分类
router.post('/delete_wlfl_category', async function (req, res) {
   let { flbm, storage } = req.body
   let db = new Msdb()
   
   try {
      // 再次检查是否可以删除（双重保险）
      const childCategories = await usequery(`select flbm, flmc from shipSRM_wlfl where flbm like '${flbm}.%'`)
      if (childCategories.length > 0) {
         const childNames = childCategories.map(item => item.flmc).join('、')
         res_util(res, {}, false, `无法删除，该分类下还有子级分类：${childNames}`)
         return
      }
      
      const categoryMaterials = await usequery(`select count(*) as count from shipSRM_wlda where lbbm like '%${flbm}%'`)
      if (categoryMaterials[0].count > 0) {
         res_util(res, {}, false, `无法删除，该分类下还有 ${categoryMaterials[0].count} 个物料`)
         return
      }
      
      // 获取分类信息用于日志记录
      const categoryInfo = await usequery(`select flmc from shipSRM_wlfl where flbm='${flbm}'`)
      if (categoryInfo.length === 0) {
         res_util(res, {}, false, "要删除的分类不存在")
         return
      }
      
      // 执行删除操作
      const delsql = `delete from shipSRM_wlfl where flbm='${flbm}'`
      await db.update(delsql)
      await saveczlog_util(delsql, storage, '', `删除物料分类"${categoryInfo[0].flmc}"`, '删除', 'shipSRM_wlfl')
      
      res_util(res, {}, true, "删除分类成功")
      
   } catch (error) {
      console.error('删除分类失败:', error)
      res_util(res, {}, false, "删除分类失败，请稍后重试")
   }
})

module.exports = router; 